select * from tblCXBrokerProvider
select * from tblProviderCompany

select top 10 * from tblCarDetailTH Order By ModifiedDate DESC

select top 10 * from tblCarMoreDetail_Kurnia Order By ModifiedDate DESC

select top 10 * from tblCXBrokerPremium Where CXBrokerProviderID = 35

select COUNT(CEID), MIN(CreatedDate), MAX(CreatedDate) from tblCEUser Where IsReject IS NULL OR IsReject != 1
select COUNT(CEID), MIN(CreatedDate), MAX(CreatedDate) from tblCEUser Where IsReject = 1
select COUNT(CEID), MIN(CreatedDate), MAX(CreatedDate) from tblCEUser

Open Symmetric Key CXUserSym
Decryption By Certificate CXUserCert
SELECT Convert(varchar(50), DECRYPTBYKEY(CU.EInfo1)) Email,CU.CEID,CU.UsrTypeId,CU.IsReject,CU.Ninfo3,CU.Info10,CU.Dinfo2,
  CL.CarId,CL.CustomerId,CL.CallStatusTypeID 
FROM tblCEUser CU INNER JOIN tblCXCRMCallStatus CL ON CL.CustomerId = CU.CEID
WHERE CL.CallStatusTypeID = 2 AND (CU.Ninfo3 IS NULL OR CU.Ninfo3 = 0)
ORDER BY CL.ModifiedDate DESC
Close Symmetric Key CXUserSym

select top 10 * from tblCarInsuranceTempResultTH Order By ModifiedDate DESC


Select Count(RefCEUserID), CarId from tblCarInsuranceTempResultTH
Where CONVERT(varchar(6), ModifiedDate , 112) = '201206'
Group By CarId
--(1781 row(s) affected)

select * from tblCarInsuranceTempResultTH Where CarId = 8759

Select Count(RefCEUserID), CarId, MAX(AnnualPremium) from tblCarInsuranceTempResultTH
Where CONVERT(varchar(6), ModifiedDate , 112) = '201206' 
Group By CarId
Having MAX(AnnualPremium) >= 10000
--(1665 row(s) affected)

Select Count(RefCEUserID), CarId, MAX(AnnualPremium) from tblCarInsuranceTempResultTH
Where CONVERT(varchar(6), ModifiedDate , 112) = '201206' 
Group By CarId
Having MAX(AnnualPremium) < 10000
--(116 row(s) affected)

Select Count(RefCEUserID), CarId, MIN(AnnualPremium) from tblCarInsuranceTempResultTH
Where CONVERT(varchar(6), ModifiedDate , 112) = '201206' 
Group By CarId
Having MIN(AnnualPremium) >= 10000
--(1143 row(s) affected)


Select Count(RefCEUserID), CarId, MIN(AnnualPremium) from tblCarInsuranceTempResultTH
Where CONVERT(varchar(6), ModifiedDate , 112) = '201206' 
Group By CarId
Having MIN(AnnualPremium) < 10000
--(638 row(s) affected)


--Declare @StartDate datetime
--DEclare @EndDate datetime

--Declare @strStartDate varchar(8)
--DEclare @strEndDate varchar(8)

--Declare @PendingStatus bit
--Select @StartDate = GETDATE() - 30
--Select @EndDate = GETDATE() - 1

--Select @strStartDate = CONVERT(varchar(8), @StartDate, 112)
--Select @strEndDate = CONVERT(varchar(8), @EndDate, 112)

--Select @StartDate, @EndDate
----(1143 row(s) affected)
--Declare @CarID bigint
--Declare @CustomerID bigint

--	Select COUNT(c.RefCEUserId) from tblCarDetailTH c 
--	INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
--	Where c.RefCEUserId NOT IN
--	(Select l.CustomerID from tblCXCRMAgentLeads l Where (Convert(varchar(8), l.CreatedDate, 112) Between @strStartDate And Convert(varchar(8), GETDATE(), 112)))
--	AND (CONVERT(varchar(8), c.CreatedDate, 112) Between @strStartDate And @strEndDate) And c.RefCEUserId > 0 
--	AND c.RefCEUserId IN (Select RefCEUserID from tblCarInsuranceTempResultTH AS T
--							Where (Convert(varchar(8), T.ModifiedDate, 112) Between @strStartDate And Convert(varchar(8), GETDATE(), 112))
--							Group By RefCEUserId
--							Having MIN(T.AnnualPremium) >= 10000)
--	ORDER BY c.CreatedDate DESC, cp.CoverageCommenceDate
	
	
--	Select top 1 @CarID = c.CarId from tblCarDetailTH c
--	INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
--	Where c.RefCEUserId = @CustomerID And (CONVERT(varchar(8), c.CreatedDate, 112) Between @strStartDate And @strEndDate) Order By CarId DESC --ORDER BY cp.CoverageCommenceDate ASC 
	
